from django.shortcuts import render, HttpResponse
from django.views.decorators.csrf import csrf_exempt
from django.db import IntegrityError,DataError
from manager.conn import *
from manager.stores import *
from manager.token import *
from manager.util import *
from manager.models import  *
from manager.stores import *
from django.conf import settings
import json
import threading
from django.db.models.functions import ExtractYear, ExtractMonth
from django.db.models.functions import Coalesce
from django.db import connection
from django.core import serializers
import pandas as pd
import os
import uuid
import json
from id_validator import validator
from apscheduler.schedulers.background import BackgroundScheduler
import time

#任务id
task_id = 0
#创建任务调度器
handler = BackgroundScheduler()
#启动任务调取器
handler.start()

#获取调度任务列表
@csrf_exempt
def get_task_list(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')


#删除调度任务
@csrf_exempt
def del_task(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    src_id = request.POST.get('id')
    DataSrc.objects.filter(id = src_id).update(status=False)
    task_id = request.POST.get('task_id')
    try:
        handler.remove_job(task_id)
        data = {
            'msg': '删除成功',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    except:
        data = {
            'msg': '删除失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')


#创建后台调度任务
@csrf_exempt
def add_task(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')

    #更新数据源状态
    src_id = request.POST.get('id')
    DataSrc.objects.filter(id = src_id).update(status=True)

    trigger_type = request.POST.get('trigger_type')
    if trigger_type == 'date':
        handler.add_job(update_source,args=(request,task_id),trigger='date',id=task_id)
    elif trigger_type == 'interval':
        min = request.POST.get('minutes')
        handler.add_job(update_source,args=(request,task_id),trigger='interval',minutes = min,id=task_id)
    elif trigger_type == 'cron':
        handler.add_job(update_source,args=(request,task_id),trigger='cron',id=task_id)
    task_id  = task_id + 1
    data = {
        'msg': '添加任务成功',
        'data': ''
    }
    return HttpResponse(json.dumps(data), status=200, content_type='application/json')

# 定义条件判断函数
def process_value(value):
    if value > 1000:
        return np.nan  # 将大于1000的值置为null
    else:
        return value

@csrf_exempt
def test(request):
    t1 = time.asctime()
    print(type(t1))
    print(t1)
    return  HttpResponse(1)

#返回已存储的测试数据
@csrf_exempt
def test_dmdata(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    # 获取数据
    store_data = get_store_data(request,user_id)
    if store_data is None:
        data = {
            'msg': '数据空/数据源异常',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        head_data = store_data.head(10)
        json_test =head_data.to_json(orient='records')
        data ={
            'msg': '返回测试数据成功',
            'data': json.loads(json_test),
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')

#返回测试数据
@csrf_exempt
def test_data(request):
    if verify_token(request) is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')

    # 获取用户源数据
    source_data = get_source_data(request)
    if source_data is None:
        data = {
            'msg': '数据空/数据源异常',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        head_data = source_data.head(10)
        json_test = head_data.to_json(orient='records')
        data ={
            'msg': '返回测试数据成功',
            'data': json.loads(json_test),
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')

#登录
@csrf_exempt
def login(request):
    if request.method == "POST":
        username = request.POST.get("username")
        password = request.POST.get("password")
        exist = Users.objects.filter(username=username).exists()
        if exist:
            obj = Users.objects.filter(username=username).first()
            admin = Users.objects.filter(username=username).first().admin
            print(admin)
            if password == obj.password:
                # 生成token
                token = generate_token(username)
                data = {
                    'msg': '登陆成功',
                    'data': token,
                    'admin' : admin
                }
                return HttpResponse(json.dumps(data), status=200, content_type='application/json')
            else:
                data = {
                    'msg':   '密码错误',
                    'data':   ''
                }
                return HttpResponse(json.dumps(data),status=400, content_type='application/json')
        else:
            data = {
                'msg': '账号错误',
                'data': ''
            }
            return HttpResponse(json.dumps(data), status=400, content_type='application/json')

#注册
@csrf_exempt
def register(request):
    """用户注册接口"""
    if request.method == "POST":
        username = request.POST.get("username")
        password = request.POST.get("password")

        # 查询账号是否已经存在
        exist = Users.objects.filter(username=username).exists()
        if exist:
            data = {
                'msg': '账号已存在',
                'data': ''
            }
            return HttpResponse(json.dumps(data), status=400, content_type='application/json')
        else:
            try:
                Users.objects.create(username=username, password=password)
            except Exception as err:
                print('注册账号异常',str(err))
                data = {
                    'msg': '注册异常',
                    'data': ''
                }
                return HttpResponse(json.dumps(data), status=400, content_type='application/json')
            data = {
                'msg': '注册成功',
                'data': ''
                }
            return HttpResponse(json.dumps(data), status=200, content_type='application/json')

#获取数据源参数
@csrf_exempt
def connect(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    
    # 采集mysql数据源
    if request.POST.get('type') == 'mysql':
        host = request.POST.get('host')
        port = int(request.POST.get('port'))
        username = request.POST.get('username')
        password = request.POST.get('password')
        database = request.POST.get('database')
        name = request.POST.get('name')

        # 查询表
        tables = col_mysql(host=host, port=port, username=username, password=password,database=database)
        
        # 数据源正常，存储数据源
        if tables is not None:         
            data = {
                'msg': '数据源正常',
                'data': tables
            }
            return HttpResponse(json.dumps(data), status=200, content_type='application/json')
        
        # 数据源异常
        else:
            data = {
                'msg':  '数据源异常',
                'data': ''
            }
            return HttpResponse(json.dumps(data),status=400, content_type='application/json')

    # 测试hdfs数据源
    if request.POST.get('type') == 'hdfs':
        host = request.POST.get('host')
        port = int(request.POST.get('port'))
        user = request.POST.get('user')
        name = request.POST.get('name')

        # 返回根路径下文件树,存储数据源
        paths = col_hdfs(hdfs_host=host, hdfs_port=port, user=user, test=True)
        if paths is not None:
            data = {
                'msg': '数据源正常',
                'data': paths
            }
            return HttpResponse(json.dumps(data), status=200, content_type='application/json')
        # 数据源异常
        else:
            data = {
                'msg': '数据源异常',
                'data': ''
            }
            return HttpResponse(json.dumps(data), status=400, content_type='application/json')

    # 采集minio数据源
    if request.POST.get('type') =='minio':
        host = request.POST.get('host')
        port = str(request.POST.get('port'))
        acc = request.POST.get('access_key')
        sec = request.POST.get('secret_key')
        bucket = request.POST.get('bucket')
        ssl = request.POST.get('ssl')
        name = request.POST.get('name')

        if ssl == 'true':
            ssl = True
        else:
            ssl = False
        
        paths = col_minio(host,port,acc,sec,bucket,test=True,use_ssl =ssl)
        # 返回根路径下文件树,存储数据源
        if paths is not None:
            data = {
                'msg': '数据源正常',
                'data': paths
            }
            return HttpResponse(json.dumps(data), status=200, content_type='application/json')
        # 数据源异常
        else:
            data = {
                'msg': '数据源异常',
                'data': ''
            }
            return HttpResponse(json.dumps(data), status=400, content_type='application/json')

# 返回存储的数据源信息
@csrf_exempt
def get_src_list(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    
    queryset = DataSrc.objects.values('id','type','name','status')
    src_list = pd.DataFrame.from_records(queryset)
    json_list = src_list.to_json(orient='records')

    data = {
        'msg': '查询成功',
        'data': json.loads(json_list),
    }
    return HttpResponse(json.dumps(data), status=200, content_type='application/json')

@csrf_exempt
def get_src_item(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')

    src_id = request.POST.get('id')
    src_obj = DataSrc.objects.filter(id=src_id).first()
    src_data = json.loads(src_obj.json)
    src_type = src_obj.type
    if src_type == 'mysql':
        # 获取数据源参数
        src_data = json.loads(src_obj.json)
        host = src_data['host']
        port = int(src_data['port'])
        username = src_data['username']
        password = src_data['password']
        database = src_data['database']

        # 查询表
        tables = col_mysql(host=host, port=port, username=username, password=password,database=database)
        
        # 数据源正常，存储数据源
        if tables is not None:         
            data = {
                'msg': '数据源正常',
                'data': tables
            }
            return HttpResponse(json.dumps(data), status=200, content_type='application/json')
    
        # 数据源异常
        else:
            data = {
                'msg':  '数据源空/异常',
                'data': ''
            }
            return HttpResponse(json.dumps(data),status=400, content_type='application/json')

    elif src_type == 'hdfs':
        host = src_data['host']
        port = int(src_data['port'])
        user = src_data['user']

        paths = col_hdfs(hdfs_host=host, hdfs_port=port, user=user, test=True)
        if paths is not None:
            data = {
                'msg': '获取文件树成功',
                'data': paths
            }
            return HttpResponse(json.dumps(data), status=200, content_type='application/json')
        # 数据源异常
        else:
            data = {
                'msg': '数据源空/异常',
                'data': ''
            }
            return HttpResponse(json.dumps(data), status=400, content_type='application/json')

    elif src_type == 'minio':
        host = src_data['host']
        port = str(src_data['port'])
        access_key = src_data['acc']
        secret_key = src_data['sec']
        bucket = src_data['bucket']
        ssl = src_data['ssl']
        print(type(ssl))
        paths = col_minio(host,port,access_key,secret_key,bucket,test=True,use_ssl =ssl)
        # 返回根路径下文件树,存储数据源
        if paths is not None:
            data = {
                'msg': '获取文件树成功',
                'data': paths
            }
            return HttpResponse(json.dumps(data), status=200, content_type='application/json')
        # 数据源异常
        else:
            data = {
                'msg': '数据源空/异常',
                'data': ''
            }
            return HttpResponse(json.dumps(data), status=400, content_type='application/json')

@csrf_exempt
def save_src(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    
    # 采集mysql数据源
    if request.POST.get('type') == 'mysql':
        host = request.POST.get('host')
        port = int(request.POST.get('port'))
        username = request.POST.get('username')
        password = request.POST.get('password')
        database = request.POST.get('database')
        name = request.POST.get('name')

        # 将数据源参数json化
        src = {
            'host' : host,
            'port' : port,
            'username' : username,
            'password' : password,
            'database' : database,
        }
        json_src = json.dumps(src)
        # 存储
        try:
            DataSrc.objects.create(type='mysql',status=False,name=name,json=json_src)
        except Exception as err:
            print(err)
            data = {
                'msg': '数据源重复',
                'data': ''
            }
            return HttpResponse(json.dumps(data), status=400, content_type='application/json')
     
    # 采集hdfs数据源
    if request.POST.get('type') == 'hdfs':
        host = request.POST.get('host')
        port = int(request.POST.get('port'))
        user = request.POST.get('user')
        name = request.POST.get('name')

        # 返回根路径下文件树,存储数据源
        src = {
            'host':host,
            'port':port,
            'user':user,
        }
        json_src = json.dumps(src)

        try:
            DataSrc.objects.create(type='hdfs',status=False,name=name,json=json_src)
        except Exception as err:
            print(err)
            data = {
                'msg': '数据源重复',
                'data': ''
            }
            return HttpResponse(json.dumps(data), status=400, content_type='application/json')

    # 采集minio数据源
    if request.POST.get('type') =='minio':
        host = request.POST.get('host')
        port = request.POST.get('port')
        acc = request.POST.get('access_key')
        sec = request.POST.get('secret_key')
        bucket = request.POST.get('bucket')
        ssl = request.POST.get('ssl')
        name = request.POST.get('name')

        if ssl == 'true':
            ssl = True
        else:
            ssl = False
        
        src = {
            'host':host,
            'port':port,
            'acc':acc,
            'sec':sec,
            'bucket':bucket,
            'ssl':ssl,
            'name':name
        }
        json_src = json.dumps(src)

        try:
            DataSrc.objects.create(type='minio',status=False,name=name,json=json_src)
        except Exception as err:
            print(err)
            data = {
                'msg': '数据源重复',
                'data': ''
            }
            return HttpResponse(json.dumps(data), status=400, content_type='application/json')

    src_obj = DataSrc.objects.filter(json=json_src).first()
    id = src_obj.id
    data = {
        'msg': '保存成功',
        'data': id
        
    }
    return HttpResponse(json.dumps(data), status=200, content_type='application/json')


# 删除数据源
@csrf_exempt
def del_src(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    src_id = int(request.POST.get('id'))
    DataSrc.objects.filter(id=src_id).delete()
    
    data = {
        'msg': '删除成功',
        'data': ''
    }
    return HttpResponse(json.dumps(data), status=200, content_type='application/json')
    
def save_uploaded_file(file, user_id):
    # 创建用户目录
    user_directory = f'storefile//{user_id}'
    os.makedirs(user_directory, exist_ok=True)

    # 生成文件名
    file_id = str(uuid.uuid4())  # 使用 UUID 作为文件名
    file_extension = file.name.split('.')[-1]
    file_name = f'{file_id}.{file_extension}'
    file_id = file_id +'.'+file_extension


    # 保存文件到用户目录
    file_path = os.path.join(user_directory, file_name)
    print(file_path)
    with open(file_path, 'wb+') as destination:
        for chunk in file.chunks():
            destination.write(chunk)

    return file_id

# 接收本地上传的文件
@csrf_exempt
def recieve(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')

    if request.method == 'POST' and request.FILES['file']:
        file = request.FILES['file']
        # 检查文件格式
        allowed_formats = ['csv', 'txt', 'xlsx','xls']
        file_extension = file.name.split('.')[-1]
        # 文件格式不符
        if file_extension not in allowed_formats:
            data = {
                'msg': '文件格式错误',
                'data': ''
            }
            return HttpResponse(json.dumps(data), status=400, content_type='application/json')
            # 处理文件
        else:
            file_name = file.name
            file_id = save_uploaded_file(file,user_id)
            data = {
                'msg': '文件上传成功',
                'file_name': file_name,
                'file_id':file_id
            }
            return HttpResponse(json.dumps(data), status=200, content_type='application/json')
    else:
        data = {
            'msg': '请求异常',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')

# 用户信息治理
@csrf_exempt
def correct_userinfo (request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    # 身份证号列名
    column_name = request.POST.get('column_name')
    # 获取用户源数据
    source_data = get_source_data(request,user_id)
    if source_data is None:
        data = {
            'msg': '数据空/数据源异常',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        correct,error = handle_userinfo(source_data,column_name)
        if correct is None and error is None:
            data ={
                'msg': '数据格式错误',
                'data': ''
            }
            return HttpResponse(json.dumps(data), status=400, content_type='application/json')
        else:
            # 存储成功治理的数据
            task = threading.Thread(target=Client_store,args=(correct,user_id))
            task.start()
            # 返回正确部分数据和错误的数据
            head_data = correct.head(10)
            json_err = error.to_json(orient='records')
            json_cor = head_data.to_json(orient='records')
            data ={
                'msg': '处理成功',
                'err_data':json.loads(json_err),
                'correct_data':json.loads(json_cor)
            }
            return HttpResponse(json.dumps(data), status=200, content_type='application/json')
            

# 物流信息治理
@csrf_exempt
def correct_logistinfo(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    # 身份证号列名
    # column_name = request.POST.get('column_name')
    # 获取用户源数据
    source_data = get_source_data(request,user_id)
    if source_data is None:
        data = {
            'msg': '数据空/数据源异常',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        correct,error = handle_logistics_info(source_data,user_id)
        if correct is None or error is None:
            data ={
                'msg': '数据格式错误',
                'data': ''
            }
            return HttpResponse(json.dumps(data), status=400, content_type='application/json')
        else:
            # 存储成功治理的数据
            task = threading.Thread(target=TradeInfo_store,args=(correct,user_id))
            task.start()
            # 返回正确部分数据和错误的数据
            head_data = correct.head(10)
            json_err = error.to_json(orient='records')
            json_cor = head_data.to_json(orient='records')
            data ={
                'msg': '处理成功',
                'err_data':json.loads(json_err),
                'correct_data':json.loads(json_cor)
            }
            return HttpResponse(json.dumps(data), status=200, content_type='application/json')

@csrf_exempt
def correct_otherinfo(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    # 获取用户源数据
    source_data = get_source_data(request,user_id)
    if source_data is None:
        data = {
            'msg': '数据空/数据源异常',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        type_data = request.POST.get('type_data')
        correct,error = handle_other_info(source_data,type_data)
        if correct is None and error is None:
            data ={
                'msg': '数据格式错误',
                'data': ''
            }
            return HttpResponse(json.dumps(data), status=400, content_type='application/json')
        else:
            # 存储成功治理的数据
            if type_data == 'jzx':
                task = threading.Thread(target=container_store,args=(correct,user_id))
                task.start()
            elif type_data =='zh':
                task = threading.Thread(target=Ship_store,args=(correct,user_id))
                task.start()
            elif type_data == 'xh':
                task = threading.Thread(target=Unship_store,args=(correct,user_id))
                task.start()
            elif type_data == 'wlgs':
                task = threading.Thread(target=TradeCompany_store,args=(correct,user_id))
                task.start()
            # 返回正确和错误的数据
            head_data = correct.head(10)
            json_err = error.to_json(orient='records')
            json_cor = head_data.to_json(orient='records')
            data ={
                'msg': '处理成功',
                'err_data':json.loads(json_err),
                'correct_data':json.loads(json_cor)
            }
            return HttpResponse(json.dumps(data), status=200, content_type='application/json')
    
#数据聚合
@csrf_exempt
def group(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')

    result_data = handle_group_info(request,user_id)
    if result_data is None:
        data = {
            'msg': '数据处理异常',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')            
    else:
        json_result = result_data.to_json(orient='records')
        data ={
            'msg': '处理结果',
            'data':json.loads(json_result),
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')

#保存修改后的用户信息
@csrf_exempt
def userinfo_reset(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')

    data = request.POST.get("data")
    user_instance = Users.objects.get(username=user_id)
    customers = json.loads(data)
    for customer_data in customers:
        try:
            customer_name = customer_data.get("客户名称")
            customer_id = customer_data.get("客户编号")
            phone_number = customer_data.get("手机号")
            location = customer_data.get("省市区")
            print(customer_name,customer_id,phone_number,location)
            Client.objects.create(name=customer_name,clientid=customer_id,tel=phone_number,addr=location,user=user_instance)  
        except Exception as err:
                print(err)
    data = {
        'msg': '保存成功',
        'data': ''
    }
    return HttpResponse(json.dumps(data), status=200, content_type='application/json')
#保存修改后的物流信息
@csrf_exempt
def logistinfo_reset(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
        
    data = request.POST.get("data")
    ret_data = json.loads(data)
    user_instance = Users.objects.get(username=user_id)

    for row_data in ret_data:
        try:
            tradeid = row_data.get("提单号")
            client = row_data.get("货主名称")
            clientid = row_data.get("货主代码")
            company = row_data.get("物流公司（货代）")
            container = row_data.get("集装箱箱号")
            goods = row_data.get("货物名称")
            weight = row_data.get("货重（吨）")
            TradeInfo.objects.create(tradeid=tradeid,client=client,clientid=clientid,company=company,container=container,goods=goods,weight=weight,user=user_instance)
        except Exception as err:
            continue
    data = {
        'msg': '保存成功',
        'data': ''
    }
    return HttpResponse(json.dumps(data), status=200, content_type='application/json')

#保存修改后的其他信息
@csrf_exempt
def otherinfo_reset(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    type_data = request.POST.get('type_data')
    data = request.POST.get("data")
    user_instance = Users.objects.get(username=user_id)

    ret_data = json.loads(data)
    if type_data == 'wlgs':
        for row_data in ret_data:
            try:
                company=row_data.get('公司名称')
                clientid =row_data.get('客户编号')
                admin=row_data.get('联系人')
                tel=row_data.get('电话')
                area=row_data.get('省市区')
                TradeCompany.objects.create(company=company,clientid=clientid,admin=admin,tel=tel,area=area,uer=user_instance)
            except Exception as err:
                continue
    elif type_data == 'zh':
        for row_data in ret_data:
            try:
                company = row_data.get('船公司')
                shipname = row_data.get('船名称')
                btime = row_data.get('作业开始时间')
                etime = row_data.get('作业结束时间')
                gotime = row_data.get('始发时间')
                artime = row_data.get('到达时间')
                harbor = row_data.get('作业港口')
                sid = row_data.get('提单号')
                con_id = row_data.get('集装箱箱号')
                size = row_data.get('箱尺寸（TEU）')
                source = row_data.get('启运地')
                destination = row_data.get('目的地')
                Ship.objects.create(company=company,shipname=shipname,btime=btime,etime=etime,gotime=gotime,artime=artime,harbor=harbor,sid=sid,con_id=con_id,size=size,source=source,destination=destination,user=user_instance)
            except Exception as err:
                continue


    elif type_data == 'xh':
        for row_data in ret_data:
            try:
                company = row_data.get('船公司')
                shipname = row_data.get('船名称')
                btime = row_data.get('作业开始时间')
                etime = row_data.get('作业结束时间')
                gotime = row_data.get('始发时间')
                artime = row_data.get('到达时间')
                harbor = row_data.get('作业港口')
                sid = row_data.get('提单号')
                con_id = row_data.get('集装箱箱号')
                size = row_data.get('箱尺寸（TEU）')
                source = row_data.get('启运地')
                destination = row_data.get('目的地')
                Unship.objects.create(company=company,shipname=shipname,btime=btime,etime=etime,gotime=gotime,artime=artime,harbor=harbor,sid=sid,con_id=con_id,size=size,source=source,destination=destination,user=user_instance)
            except Exception as err:
                continue

    elif type_data == 'jzx':
        for row_data in ret_data:
            try:
                harbor = row_data.get('作业港口')
                con_id = row_data.get('集装箱箱号')
                size = row_data.get('箱尺寸（TEU）')
                sid = row_data.get('提单号')
                store = row_data.get('堆场位置')
                op = row_data.get('操作')
                opdata = row_data.get('操作日期')
                Container.objects.create(harbor=harbor,con_id=con_id,size=size,sid=sid,store=store,op=op,opdata=opdata,user=user_instance)
            except Exception as err:
                continue
    data = {
        'msg': '保存成功',
        'data': ''
    }
    return HttpResponse(json.dumps(data), status=200, content_type='application/json')

#处理异常数据
@csrf_exempt
def handle(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    out_data = handle_out(request,user_id)
    if out_data is None:
        data = {
            'msg': '无异常数据',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')
    elif out_data is False:
        data = {
            'msg': '列名选择错误',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')    
    else:
        json_data = out_data.to_json(orient='records')
        data = {
            'msg': '数据检测成功',
            'data': json.loads(json_data)
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')  



# 港口吞吐量
@csrf_exempt
def port_io(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    
    data = port_throughput(request,user_id)
    if data is None:
        data = {
            'msg': '暂无数据',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        json_data = data.to_json(orient='records')
        data = {
            'msg': '处理成功',
            'data': json.loads(json_data)
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')   
    
# 港口不同货物吞吐量
@csrf_exempt
def port_goods_io(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    in_data,out_data = port_goods_throughput(request,user_id)

    json_in = in_data.to_json(orient='records')
    json_out = out_data.to_json(orient='records')
    if json_in is None and json_out is None:
        data = {
            'msg': '暂无数据',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        data = {
            'msg': '处理成功',
            'in_data': json.loads(json_in),
            'out_data':json.loads(json_out)
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')

# 不同货物流向
@csrf_exempt
def flow(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    result = flow_analysis(request,user_id)
    if result is None:
        data = {
            'msg': '暂无数据',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        json_data = result.to_json(orient='records')
        data = {
            'msg': '处理成功',
            'data': json.loads(json_data)
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')   

# 同比环比
@csrf_exempt
def round(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    result = round_analysis(request,user_id)
    if result is None:
        data = {
            'msg': '暂无数据',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        json_data = result.to_json(orient='records')
        data = {
            'msg': '处理成功',
            'data': json.loads(json_data)
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')

# 不同港口不同货物的吞吐趋势
@csrf_exempt
def goods_tendency(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    result = port_goods_tendency(request,user_id)
    if result is None:
        data = {
            'msg': '暂无数据',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        json_data = result.to_json(orient='records')
        data = {
            'msg': '处理成功',
            'data': json.loads(json_data),
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')

# 查询港口和年份
@csrf_exempt
def get_port_year(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    try:
        print(user_id)
        harbors = list(Container.objects.filter(user=user_id).values_list('harbor', flat=True).distinct())
        years = list(Container.objects.filter(user=user_id).annotate(year=ExtractYear('opdata')).values_list('year', flat=True).distinct())
        data = {
            'msg': '查询成功',
            'ports':harbors,
            'years':years
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')

    except Exception as err:
        print(str(err))
        data = {
            'msg': '查询失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    
# 查询货物种类
@csrf_exempt
def get_goods(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    try:
        goods = list(TradeInfo.objects.filter(user=user_id).values_list('goods', flat=True).distinct())

        data = {
            'msg': '查询成功',
            'data': goods
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')

    except Exception as err:
        print(str(err))
        data = {
            'msg': '查询失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')

# 得到物流景气指数  
@csrf_exempt   
def get_prosperity(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    result = cal_prosperity(request,user_id)
    if result is None:
        data = {
            'msg': '暂无数据',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        json_data = result.to_json(orient='records')
        data = {
            'msg': '处理成功',
            'data': json.loads(json_data),
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')

# 得到不同货物堆场流转周期 
@csrf_exempt   
def get_cycle(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    result = cycle(request,user_id)
    if result is None:
        data = {
            'msg': '暂无数据',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        json_data = result.to_json(orient='records')
        data = {
            'msg': '处理成功',
            'data': json.loads(json_data),
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')
    
#返回用户列表
@csrf_exempt   
def get_users(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')

    #查询普通用户列表
    queryset = Users.objects.filter(admin = False).values('username','id','name','share')
    if queryset is None:
        data = {
            'msg': '查询用户失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        df = pd.DataFrame.from_records(queryset)
        df.rename(columns={'username':'用户id','id':'身份证号','name':'姓名','share':'共享权限'}, inplace=True)
        json_df = df.to_json(orient='records')
        data = {
            'msg': '查询用户成功',
            'data': json.loads(json_df)
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')

#更新共享权限
@csrf_exempt   
def update_share(request):
    # 验证token
    userid = verify_token(request)
    if userid is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    share = request.POST.get('share')
    if share =='true':
        share = True
    else:
        share = False
    user_id = request.POST.get('user_id')
    Users.objects.filter(username = user_id).update(share=share)
    data = {
        'msg': '修改成功',
        'data': ''
    }
    return HttpResponse(json.dumps(data), status=200, content_type='application/json')

#检查共享权限
@csrf_exempt   
def check(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    try:
        obj = Users.objects.filter(username=user_id).first()
        print(obj.share)
        if obj.share is True:
            data = {
                'msg': '访问成功',
                'data': ''
            }
            return HttpResponse(json.dumps(data), status=200, content_type='application/json')
    except Exception as err:
        print('def check',str(err))
        data = {
            'msg': '查询异常',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        data = {
            'msg': '您没有访问权限',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')

#申请共享权限
@csrf_exempt   
def apply(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    id = request.POST.get('id')
    name = request.POST.get('name')
    if validator.is_valid(id) is False:
        data = {
            'msg': '身份证号错误',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        Users.objects.filter(username=user_id).update(id=id,name=name,share=True)
        data = {
            'msg': '申请共享权限成功',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')
    
#返回开通数据共享的用户
@csrf_exempt   
def getlist(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    queryset = Users.objects.filter(share=True,admin=False).values('username')
    share_list = [item['username'] for item in queryset]
    if share_list is None:
        data = {
            'msg': '暂无共享用户',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')
    else:
        data = {
            'msg': '查询共享用户成功',
            'data': share_list
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')

@csrf_exempt     
def share_data(request):
    # 验证token
    user_id = verify_token(request)
    if user_id is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }  
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    share_id = request.POST.get('user_id')
    share_data = get_store_data(request,share_id)
    if share_data is None:
        data = {
            'msg': '查询异常',
            'data': ''
        }
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
        json_test =share_data.to_json(orient='records')
        data ={
            'msg': '查询成功',
            'data': json.loads(json_test),
        }
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')

@csrf_exempt 
def export(request):
    # 验证token
    userid = verify_token(request)
    if userid is None:
        data = {
            'msg': 'token验证失败',
            'data': ''
        }  
        return HttpResponse(json.dumps(data), status=400, content_type='application/json') 
    data = export_data(request)
    if data is None:
        data = {
            'msg': '导出数据异常',
            'data': ''
        }  
        return HttpResponse(json.dumps(data), status=400, content_type='application/json')
    else:
    

        # 生成唯一的文件名
        file_name = f"{uuid.uuid4()}.csv"
        file_dic = 'manager//static//tempfile'
        # 构建文件的完整路径
        file_path = os.path.join(file_dic, file_name)

        # 将DataFrame保存为CSV文件
        data.to_csv(file_path, index=False)

        # 返回文件的下载链接给前端

        file_url = 'http://36.140.31.145:32017/static/tempfile/' + file_name

        data = {
            'msg': '导出数据成功',
            'url': file_url
        }  
        return HttpResponse(json.dumps(data), status=200, content_type='application/json')